<?php
/**
 * 同步更新备货（总表数据）
 * User: bgl
 * Date: 2023/5/11
 * Time: 14:31
 */

namespace App\Logic\ProductStockup;


use App\Models\ProductStockup\Stockup;
use Illuminate\Support\Facades\DB;

class SyncStockup{

    /**
     * 更新生成库存（总表）
     */
    public static function syncUpdateStockup(){

        die('stop');

        $data = DB::table('s_area_sku as area_sku')
            ->leftJoin('s_area_sku_avg as avg','area_sku.area_psku', '=', 'avg.area_psku')
            ->leftJoin('s_area_sku_estimate as sku_estimate','area_sku.area_psku','=','sku_estimate.area_psku')
            ->leftJoin('s_area_sku_stock as stock','area_sku.area_psku','=','stock.area_psku')
            ->select(
                'area_sku.id', 'area_sku.area_psku', 'area_sku.type',
                'avg.avg3', 'avg.avg7', 'avg.avg15', 'avg.avg30', 'avg.avg',
                DB::raw('
                    IFNULL(sku_estimate.estimate_num0, 0) as estimate_num0,
                    IFNULL(sku_estimate.estimate_num1, 0) as estimate_num1,
                    IFNULL(sku_estimate.estimate_num2, 0) as estimate_num2,
                    IFNULL(sku_estimate.estimate_num3, 0) as estimate_num3,
                    IFNULL(sku_estimate.estimate_num4, 0) as estimate_num4,
                    IFNULL(sku_estimate.estimate_num5, 0) as estimate_num5,
                    IFNULL(sku_estimate.estimate_num6, 0) as estimate_num6,
                    IFNULL(sku_estimate.estimate_num7, 0) as estimate_num7,
                    IFNULL(sku_estimate.estimate_num8, 0) as estimate_num8
                '),
                'stock.fba_instant_num', 'stock.fba_transit_num', 'stock.cn_instant_num', 'stock.cn_transit_num', 'stock.os_instant_num', 'stock.os_transit_num'
            )
            ->orderBy('id')
            //->limit(10)
            ->get();

        if ($data) {

            //删除差异数据
            $res1 = array_column(json_decode($data, true), 'area_psku'); //area_psku基础数据
            $res2 = Stockup::query()->pluck('area_psku')->toArray(); //备货基础数据
            $diffArrayData = array_diff($res1, $res2);
            if ($diffArrayData) {
                foreach ($diffArrayData as $key => $value){
                    Stockup::query()->where('area_psku',$value)->delete();
                }
            }

            //更新备货数据
            $n = 0;
            DB::beginTransaction();
            try{
                //2913
                foreach ($data as $key => $val){

                    if ($val->id == 3) {
                        continue;
                    }

                    $paramData = Stockup::setStockupColumn($val);
                    if (!$paramData) continue;

                    $res = Stockup::query()->updateOrCreate([
                        'area_psku' => $val->area_psku
                    ],[
                        'area_id'               => $paramData->area_id,
                        'psku_id'               => $paramData->psku_id,
                        'classify_id'           => $paramData->classify_id,
                        'cn_pulic_instant_num'  => $paramData->cn_pulic_instant_num ?? 0,
                        'overstock_num'         => $paramData->overstock_num ?? 0,
                        'cn_stock_total'        => $paramData->cn_stock_total ?? 0,
                        'divert_overstock_num'  => $paramData->divert_overstock_num ?? 0,
                        'divert_instant_num'    => $paramData->divert_instant_num ?? 0,
                        'divert_transit_num'    => $paramData->divert_transit_num ?? 0,
                        'purchase_cycle'        => $paramData->purchase_cycle ?? 0,
                        'sea_day'               => $paramData->sea_day ?? 0,
                        'air_day'               => $paramData->air_day ?? 0,
                        'defer_stock_num'       => $paramData->defer_stock_num ?? 0,

                        'avg3'  => $val->avg3 ?? 0,
                        'avg7'  => $val->avg7 ?? 0,
                        'avg15' => $val->avg15 ?? 0,
                        'avg30' => $val->avg30 ?? 0,
                        'avg'   => $val->avg ?? 0,
                        'current_sale_ratio' => $paramData->current_sale_ratio ?? 0,
                        'volume30_ratio'     => $paramData->volume30_ratio ?? 0,

                        'estimate_num0'  => $val->estimate_num0 ?? 0,
                        'estimate_num1'  => $val->estimate_num1 ?? 0,
                        'estimate_num2'  => $val->estimate_num2 ?? 0,
                        'estimate_num3'  => $val->estimate_num3 ?? 0,
                        'estimate_num4'  => $val->estimate_num4 ?? 0,
                        'estimate_num5'  => $val->estimate_num5 ?? 0,
                        'estimate_num6'  => $val->estimate_num6 ?? 0,
                        'estimate_num7'  => $val->estimate_num7 ?? 0,
                        'estimate_num8'  => $val->estimate_num8 ?? 0,
                        'estimate_total' => $paramData->estimate_total ?? 0,

                        'cn_delivery_num0'  => $paramData->cn_delivery_num0 ?? 0,
                        'cn_delivery_num1'  => $paramData->cn_delivery_num1 ?? 0,
                        'cn_delivery_num2'  => $paramData->cn_delivery_num2 ?? 0,
                        'cn_delivery_num3'  => $paramData->cn_delivery_num3 ?? 0,
                        'cn_delivery_num4'  => $paramData->cn_delivery_num4 ?? 0,
                        'cn_delivery_num5'  => $paramData->cn_delivery_num5 ?? 0,
                        'cn_delivery_num6'  => $paramData->cn_delivery_num6 ?? 0,
                        'cn_delivery_num7'  => $paramData->cn_delivery_num7 ?? 0,
                        'cn_delivery_num8'  => $paramData->cn_delivery_num8 ?? 0,

                        'fba_instant_num' => $val->fba_instant_num ?? 0,
                        'fba_transit_num' => $val->fba_transit_num ?? 0,
                        'cn_instant_num'  => $val->cn_instant_num ?? 0,
                        'cn_transit_num'  => $val->cn_transit_num ?? 0,
                        'os_instant_num'  => $val->os_instant_num ?? 0,
                        'os_transit_num'  => $val->os_transit_num ?? 0,
                        'stock_total'     => $paramData->stock_total ?? 0,
                    ]);

                    if (!$res) {
                        DB::rollBack();
                        continue;
                    }

                    $n++;
                    echo '......'.$n.'......'.PHP_EOL;
                }

                DB::commit();
            }catch (\Exception $e){
                DB::rollBack();
                report($e);
            }
        }

        //更新分配库存数量
        //self::saveStockPlan();

        echo "<pre>";
        print_r('over');
        echo "</pre>";
        exit;
    }

    /**
     * 更新分配库存（回写）
     */
    public static function saveStockPlan(){
        //回写，需求分配库存(月份)
        $n = 0;
        $stockup = Stockup::query()
            ->select('area_psku','psku_id','classify_id', DB::raw('
                sum(fba_instant_num) as fba_instant_num,
                sum(fba_transit_num) as fba_transit_num,
                sum(os_instant_num) as os_instant_num,
                sum(os_transit_num) as os_transit_num,
                sum(cn_instant_num) as cn_instant_num
            '))
            ->groupBy('psku_id')
            ->orderBy('id')
            //->limit(100)
            ->get();
        if ($stockup) {
            $data = [];
            foreach ($stockup as $key => $val){
                $total = $val->fba_instant_num + $val->fba_transit_num + $val->os_instant_num + $val->os_transit_num + $val->cn_instant_num;
                $data[$val->psku_id]['stock_total'] = $total; //库存量
                for ($i = 0; $i <= 8; $i++){
                    //月份发货需求数量（当月的总需求量）
                    $data[$val->psku_id][$i]['cn_delivery_num'.$i] = Stockup::getStockupColumnSum($val->classify_id, 'cn_delivery_num'.$i);

                    //月份发货站点（当月站点的需求量）
                    $areaNumber = Stockup::query()->where('psku_id', $val->psku_id)->pluck('cn_delivery_num'.$i, 'area_id');
                    foreach ($areaNumber as $k => $v){
                        $data[$val->psku_id][$i]['area'][$k] = $v;
                    }
                }

                $paramData = self::setStockPlanColumn($data); //回写库存分配
                $res = Stockup::query()->updateOrCreate([
                    'area_psku' => $val->area_psku
                ],[
                    'stock_plan0'  => $paramData->stock_plan0 ?? 0,
                    'stock_plan1'  => $paramData->stock_plan1 ?? 0,
                    'stock_plan2'  => $paramData->stock_plan2 ?? 0,
                    'stock_plan3'  => $paramData->stock_plan3 ?? 0,
                    'stock_plan4'  => $paramData->stock_plan4 ?? 0,
                    'stock_plan5'  => $paramData->stock_plan5 ?? 0,
                    'stock_plan6'  => $paramData->stock_plan6 ?? 0,
                    'stock_plan7'  => $paramData->stock_plan7 ?? 0,
                    'stock_plan8'  => $paramData->stock_plan8 ?? 0,
                ]);

                if (!$res) {
                    DB::rollBack();
                    continue;
                }

                $n++;
                echo 'stock_plan......'.$n.'......'.PHP_EOL;
            }
        }
    }

    /**
     * 获取, 需求分配库存(月份)
     * @param $data
     * @return array
     */
    public static function setStockPlanColumn($data){

        //需分配库存
        $params = [];
        foreach ($data as $key => $val){

            $stockTotal = $val['stock_total'] ?? 0; //库存总量
            unset($val['stock_total']);

            foreach ($val as $k => $v){

                $cnMonthNumber = $v['cn_delivery_num'.$k] ?? 0; //当前月
                //首月分配库存数量
                if ($k == 0) {
                    if ($cnMonthNumber <= 0) { //首月中国发货需求数量
                        //首月中国发货需求数量小于0, 则分配库存数量 = 0
                        $params['stock_plan'.$k] = 0;
                    }else{
                        //首月分配库存数量 = （首月中国发货需求数量 - fba在途 - 海外仓在途）
                        $params['stock_plan'.$k] = $cnMonthNumber - $stockTotal;
                    }
                }

                //其它月份分配库存数量
                if ($k > 0) {
                    if ($params['stock_plan'.($k-1)] < 0) { //上月分配数量 < 0, 则上月分配数量累加当月中国发货需求数量。
                        //当月分配数量 = (上月分配数量 + 当月中国发货需求数量)
                        $params['stock_plan'.$k] = $params['stock_plan'.($k-1)] + $cnMonthNumber;

                    }else{
                        //当月分配数量 = 月份发货需求数量
                        $params['stock_plan'.$k] = $cnMonthNumber;
                    }
                }
            }
        }

        return json_decode(json_encode($params));
    }

}
